﻿using DotNetCommon.Data;
using DotNetCommon.Extensions;
using DotNetCommon;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Expressions;
using System.Text;
using System.Threading.Tasks;

namespace DBUtil.Builders;

internal class EntityAlias
{
    public EntityInfo EntityInfo;
    public string Alias { get; set; }
    public bool IsAssign { get; set; }
}
internal class FromJoin
{
    public LambdaExpression Expression { get; set; }
    public EnumJoinType Type { get; set; }
}
public class SelectBuilderBase : WhereBuilder
{
    internal List<EntityAlias> EntityAliases;
    internal List<FromJoin> FromJoins;
    internal SelectBuilderBase(DBAccess db) : base(db) { }

    #region AsTable
    public virtual SelectBuilderBase AsTable(Func<string, string> func)
    {
        var newTableName = func?.Invoke(EntityAliases.Last().EntityInfo.TableName.Name);
        if (newTableName.IsNotNullOrWhiteSpace())
        {
            var entityAlias = EntityAliases.Last();
            entityAlias.EntityInfo.TableName.SetName(newTableName);
        }
        return this;
    }

    public virtual SelectBuilderBase AsTableIf(bool condition, Func<string, string> func)
        => condition ? AsTable(func) : this;
    public virtual SelectBuilderBase AsTable(Action<TableName> func)
    {
        func?.Invoke(EntityAliases.Last().EntityInfo.TableName);
        return this;
    }

    public virtual SelectBuilderBase AsTableIf(bool condition, Action<TableName> func)
        => condition ? AsTable(func) : this;
    #endregion

    #region Alias
    public SelectBuilderBase Alias(string alias)
    {
        AssertUtil.NotNullOrWhiteSpace(alias);
        var entityAlias = EntityAliases.Last();
        entityAlias.Alias = alias;
        entityAlias.IsAssign = true;
        return this;
    }
    #endregion

    #region Where
    protected SelectBuilderBase Where(LambdaExpression expression)
    {
        AssertUtil.NotNull(expression);
        Filters.Add(expression);
        return this;
    }
    protected SelectBuilderBase WhereIf(bool condition, LambdaExpression expression)
    {
        if (condition)
        {
            AssertUtil.NotNull(expression);
            Filters.Add(expression);
        }
        return this;
    }
    #endregion

    #region Distinct
    protected bool isDistinct;
    protected SelectBuilderBase Distinct(bool isDistinct = true)
    {
        this.isDistinct = isDistinct;
        return this;
    }
    #endregion

    #region 排序
    protected List<(object obj, bool desc)> orderBys = [];
    protected SelectBuilderBase OrderBy(LambdaExpression expression)
    {
        orderBys.Add((expression, false));
        return this;
    }

    protected SelectBuilderBase OrderByDesc(LambdaExpression expression)
    {
        orderBys.Add((expression, true));
        return this;
    }

    /// <summary>
    /// 排序,如: select.Order("age desc,id")
    /// </summary>
    protected SelectBuilderBase Order(string orderSeg)
    {
        orderBys.Add((orderSeg, false));
        return this;
    }
    #endregion

    #region 分页
    protected int? pageIndex = null;
    protected int? pageSize = null;
    protected int? limitSize = null;
    protected int? limitStartIndex = null;
    protected SelectBuilderBase Page(int pageIndex, int pageSize)
    {
        AssertUtil.BeTrue(pageIndex >= 1, $"{nameof(pageIndex)}必须从1开始!");
        AssertUtil.BeTrue(pageSize > 0, $"{nameof(pageSize)}必须大于0!");
        this.pageIndex = pageIndex;
        this.pageSize = pageSize;
        this.limitSize = this.limitStartIndex = null;
        return this;
    }

    private SelectBuilderBase limit(int size, int startIndex)
    {
        AssertUtil.BeTrue(startIndex >= 0, $"{nameof(startIndex)}必须大于等于0!");
        AssertUtil.BeTrue(size >= 0, $"{nameof(size)}必须大于等于0!");
        this.limitSize = size;
        this.limitStartIndex = startIndex;
        this.pageIndex = this.pageSize = null;
        return this;
    }
    protected SelectBuilderBase Limit(int size) => this.limit(size, 0);
    protected SelectBuilderBase Limit(int startIndex, int size) => this.limit(size, startIndex);
    #endregion

    #region WithSql
    protected string fromSql = string.Empty;
    protected SelectBuilderBase WithSql(string fromSql)
    {
        this.fromSql = fromSql;
        return this;
    }
    #endregion

    #region AddExtraColumnSelect
    private List<string> extraSelectSegs = new();
    internal virtual SelectBuilderBase AddExtraSelectSeg(string seg)
    {
        extraSelectSegs.Add(seg);
        return this;
    }
    #endregion

    #region ToSql
    internal string ToSqlFirst(LambdaExpression expression) => Limit(1).ToSql(EnumSelectToSql.ToList, expression);
    internal string ToSqlFirstDto(List<(string dtoCol, EntityPropertyInfo entPropInfo)> dtoMap) => Limit(1).ToSql(EnumSelectToSql.ToList, dtoMap: dtoMap);
    internal string ToSqlList(LambdaExpression expression) => ToSql(EnumSelectToSql.ToList, expression);
    internal string ToSqlSelectClause(LambdaExpression expression) => ToSql(EnumSelectToSql.ToSelectClause, expression);
    internal string ToSqlListDto(List<(string dtoCol, EntityPropertyInfo entPropInfo)> dtoMap) => ToSql(EnumSelectToSql.ToList, dtoMap: dtoMap);
    internal string ToSqlPage(LambdaExpression expression) => ToSql(EnumSelectToSql.ToPage, expression);
    internal string ToSqlPageDto(List<(string dtoCol, EntityPropertyInfo entPropInfo)> dtoMap) => ToSql(EnumSelectToSql.ToPage, dtoMap: dtoMap);
    public string ToSqlCount() => ToSql(EnumSelectToSql.Count);

    protected string ToSql(EnumSelectToSql enumSelectToSql, LambdaExpression expression = null, Dictionary<ParameterExpression, object> midValues = null, IEnumerable<KeyValuePair<ParameterExpression, string>> aliases = null, List<(string dtoCol, EntityPropertyInfo entPropInfo)> dtoMap = null)
    {
        var sb = new StringBuilder();
        string tmp;
        switch (enumSelectToSql)
        {
            case EnumSelectToSql.Count:
                sb.Append("select count(1)");
                break;
            case EnumSelectToSql.ToList:
            case EnumSelectToSql.ToPage:
                tmp = GetSelectSql(expression, midValues, dtoMap);
                sb.Append($"select{(isDistinct ? " distinct " : " ")}{tmp}");
                if (extraSelectSegs.IsNotNullOrEmpty())
                {
                    var seg = extraSelectSegs.ToStringSeparated(",");
                    sb.Append(',').Append(seg);
                }
                break;
            case EnumSelectToSql.ToSelectClause:
                tmp = GetSelectSql(expression, midValues, dtoMap);
                return tmp;
            case EnumSelectToSql.Max:
                tmp = GetSelectSql(expression, midValues);
                sb.Append($"select max({tmp})");
                break;
            case EnumSelectToSql.Min:
                tmp = GetSelectSql(expression, midValues);
                sb.Append($"select min({tmp})");
                break;
            case EnumSelectToSql.Sum:
                tmp = GetSelectSql(expression, midValues);
                sb.Append($"select sum({tmp})");
                break;
            case EnumSelectToSql.Avg:
                tmp = GetSelectSql(expression, midValues);
                sb.Append($"select avg({tmp})");
                break;
        }
        var fromSql = GeneFromSql();
        sb.AppendLine().Append(fromSql);

        //过滤
        var whereSql = GeneWhereSql(midValues, aliases);
        if (whereSql.IsNotNullOrWhiteSpace()) sb.AppendLine().Append(whereSql);

        //排序
        var orderSql = GetOrderSql();
        if (orderSql.IsNotNullOrWhiteSpace()) sb.AppendLine().Append(orderSql);

        //分页
        var limitSeg = GetLimit();
        if (limitSeg.IsNotNullOrWhiteSpace()) sb.AppendLine().Append(limitSeg);
        var countSql = string.Empty;
        if (enumSelectToSql == EnumSelectToSql.ToPage)
        {
            var sb2 = new StringBuilder();
            sb2.Append("select count(1)").AppendLine().Append(fromSql);
            if (whereSql.IsNotNullOrWhiteSpace()) sb2.AppendLine().Append(whereSql);
            if (orderSql.IsNotNullOrWhiteSpace()) sb2.AppendLine().Append(orderSql);
            sb2.Append(";");
            countSql = sb2.ToString();
            sb2.Clear();
        }

        sb.Append(';');
        if (countSql.IsNotNullOrWhiteSpace()) sb.AppendLine().Append(countSql);
        var sql = sb.ToString();
        sb.Clear();
        return sql;
    }

    internal string GetSelectSql(LambdaExpression expression, Dictionary<ParameterExpression, object> midValues, List<(string dtoCol, EntityPropertyInfo entPropInfo)> dtoMap = null)
    {
        if (dtoMap != null)
        {
            //entity 映射到 dto
            var cols = dtoMap.OrderBy(i => i.entPropInfo.Order)
                .Select(i =>
                {
                    var _val = i.entPropInfo.SelectValueFunc(EntityAliases.First().Alias);
                    if (i.entPropInfo.IsColumnNameEqualPropName)
                        if (_val.EndsWith(i.dtoCol)) return _val;
                    return $"{_val} {i.dtoCol}";
                })
                .ToStringSeparated(",");
            return cols;
        }
        else if (expression == null)
        {
            //直接查询所有的列
            var cols = EntityAliases.First().EntityInfo.Props
                .Where(i => !i.IsIgnoreSelect)
                .OrderBy(i => i.Order)
                .Select(i => i.SelectFunc(EntityAliases.First().Alias))
                .ToStringSeparated(",");
            return cols;
        }
        else
        {
            //查询指定的列从表达式语句中提取
            Expression exp = expression;
            if (midValues.IsNullOrEmpty())
                (exp, midValues) = ExpressionHelper.ReduceLambda(expression, isKeepCallBack: BuilderHelper.IsKeepCallBackForSubSelect);
            if (exp is ConstantExpression) throw new Exception($"多余的Select语法(可能使用了ToList(i=>constExp))!");
            var val = BuilderHelper.ParseSql(exp, db, aliasesMap: EntityAliases.Select((i, idx) => new KeyValuePair<ParameterExpression, string>(expression.Parameters[idx], EntityAliases[idx].Alias)).ToList(), midValues: midValues);
            return val;
        }
    }
    internal string GetOrderSql()
    {
        if (this.orderBys.IsNotNullOrEmpty())
        {
            var segs = new List<string>();
            for (int i = 0; i < this.orderBys.Count; i++)
            {
                var orderBy = this.orderBys[i];
                if (orderBy.obj is string str)
                {
                    segs.Add(str);
                    continue;
                }
                var descSeg = this.orderBys[i].desc ? " desc" : "";
                var lambda = orderBy.obj as LambdaExpression;

                var (exp, midValues) = ExpressionHelper.ReduceLambda(lambda, isKeepCallBack: BuilderHelper.IsKeepCallBackForSubSelect);
                lambda = exp as LambdaExpression;
                var aliasesMap = EntityAliases.Select((i, idx) => new KeyValuePair<ParameterExpression, string>(lambda.Parameters[idx], i.Alias));
                var isNewOrMemberInit = false;
                var seg = BuilderHelper.ParseSql(exp, db,
                     aliasesMap: aliasesMap,
                     parameters: lambda.Parameters,
                     midValues: midValues,
                     nodeCallBacks:
                     [ new NodeCallBack
                         (
                            exp => exp.NodeType == ExpressionType.New || exp.NodeType == ExpressionType.MemberInit,
                            (exp, next, visit) =>
                            {
                                isNewOrMemberInit = true;
                                if(exp.NodeType == ExpressionType.New)
                                {
                                    var newExp = exp as NewExpression;
                                    var args = newExp.Arguments;
                                    var members = newExp.Members;
                                    var list = new List<string>();
                                    for (int i = 0; i < args.Count; i++)
                                    {
                                        var arg = args[i];
                                        var _val = visit(arg);
                                        list.Add(_val+descSeg);
                                    }
                                    return list.ToStringSeparated(",");
                                } else{
                                    var initExp = exp as MemberInitExpression;
                                    var bindings = initExp.Bindings;
                                    var list = new List<string>();
                                    for (int i = 0; i < bindings.Count; i++)
                                    {
                                        var binding = bindings[i];
                                        var assign = binding as MemberAssignment;
                                        var exp2 = assign.Expression;
                                        var  _val = visit(exp2);
                                        list.Add(_val+descSeg);
                                    }
                                    return list.ToStringSeparated(",");
                                }
                            }
                         )
                     ]);
                if (!isNewOrMemberInit) seg += descSeg;
                segs.Add(seg);
            }
            return $"order by {segs.ToStringSeparated(",")}";
        }
        return string.Empty;
    }
    internal string GeneFromSql()
    {
        if (fromSql.IsNotNullOrWhiteSpace()) return $"from ({fromSql})";
        if (EntityAliases.Count == 1) return $"from {EntityAliases.First().EntityInfo.TableName.FullNameQuoted} {EntityAliases.First().Alias}";
        //join
        var sb = new StringBuilder();
        for (int i = 0; i < EntityAliases.Count; i++)
        {
            var entityAlias = EntityAliases[i];
            if (i == 0)
            {
                sb.Append($"from {entityAlias.EntityInfo.TableName.FullNameQuoted} {entityAlias.Alias}");
            }
            else
            {
                var join = FromJoins[i - 1];
                sb.AppendLine().Append($"    {GetJoinTypeString(join.Type)} {entityAlias.EntityInfo.TableName.FullNameQuoted} {entityAlias.Alias}");
                if (join.Expression == null) continue;
                sb.Append(" on ");
                //条件
                var (exp, midValues) = ExpressionHelper.ReduceLambda(join.Expression, isKeepCallBack: BuilderHelper.IsKeepCallBackForSubSelect);
                var sql = BuilderHelper.ParseSql(exp, db,
                     aliasesMap: join.Expression.Parameters.Select((i, idx) => new KeyValuePair<ParameterExpression, string>(i, EntityAliases[idx].Alias)).ToList(),
                     parameters: join.Expression.Parameters,
                     midValues: midValues);
                sb.Append(sql);
            }
        }
        return sb.ToString();
    }
    internal string GetJoinTypeString(EnumJoinType joinType)
    {
        switch (joinType)
        {
            case EnumJoinType.LeftJoin:
                return "left join";
            case EnumJoinType.RightJoin:
                return "right join";
            case EnumJoinType.InnerJoin:
                return "inner join";
            case EnumJoinType.CrossJoin:
                return "cross join";
            default:
                throw new Exception($"错误的Join类型: {joinType}");
        }
    }
    internal string GeneWhereSql(Dictionary<ParameterExpression, object> midValues = null, IEnumerable<KeyValuePair<ParameterExpression, string>> aliases = null)
    {
        if (Filters.IsNotNullOrEmpty())
        {
            //应用 EntityAliases 上记录的别名
            var newAliases = new List<KeyValuePair<ParameterExpression, string>>();
            if (aliases.IsNotNullOrEmpty()) newAliases.AddRange(aliases);
            foreach (var item in Filters)
            {
                if (item is LambdaExpression lambda)
                {
                    if (lambda.Parameters.Count == EntityAliases.Count)
                    {
                        var zips = lambda.Parameters.Zip(EntityAliases);
                        if (zips.All(i => i.First.Type == i.Second.EntityInfo.Type))
                        {
                            zips.ForEach(zip => newAliases.Add(new KeyValuePair<ParameterExpression, string>(zip.First, zip.Second.Alias)));
                        }
                    }
                }
            }
            var filterSql = DealFilter(Filters, midValues, newAliases);
            return $"where {filterSql}";
        }
        return string.Empty;
    }
    internal string GetLimit()
    {
        var startIndex = this.limitStartIndex >= 0 ? this.limitStartIndex : this.pageSize > 0 ? ((this.pageIndex - 1) * this.pageSize) : -1;
        var pageSize = this.limitStartIndex >= 0 ? this.limitSize : this.pageSize > 0 ? this.pageSize : -1;
        if (startIndex > 0)
        {
            return $"limit {startIndex},{pageSize}";
        }
        else if (startIndex == 0)
        {
            return $"limit {pageSize}";
        }
        return null;
    }
    #endregion

    #region 聚合 & 聚合sql
    #region 聚合Sql
    protected string MaxSql<TKey>(LambdaExpression expression) => ToSql(EnumSelectToSql.Max, expression);
    protected string MinSql<TKey>(LambdaExpression expression) => ToSql(EnumSelectToSql.Min, expression);
    protected string SumSql<TKey>(LambdaExpression expression) => ToSql(EnumSelectToSql.Sum, expression);
    protected string AvgSql<TKey>(LambdaExpression expression) => ToSql(EnumSelectToSql.Avg, expression);
    #endregion

    #region 聚合同步
    protected TKey MaxOrDefault<TKey>(LambdaExpression expression) => db.SelectScalarOrDefault<TKey>(MaxSql<TKey>(expression));
    protected TKey MinOrDefault<TKey>(LambdaExpression expression) => db.SelectScalarOrDefault<TKey>(MinSql<TKey>(expression));
    protected TKey SumOrDefault<TKey>(LambdaExpression expression) => db.SelectScalarOrDefault<TKey>(SumSql<TKey>(expression));
    protected TKey AvgOrDefault<TKey>(LambdaExpression expression) => db.SelectScalarOrDefault<TKey>(AvgSql<TKey>(expression));
    #endregion

    #region 聚合异步
    protected async Task<TKey> MaxOrDefaultAsync<TKey>(LambdaExpression expression) => await db.SelectScalarOrDefaultAsync<TKey>(MaxSql<TKey>(expression));
    protected async Task<TKey> MinOrDefaultAsync<TKey>(LambdaExpression expression) => await db.SelectScalarOrDefaultAsync<TKey>(MinSql<TKey>(expression));
    protected async Task<TKey> SumOrDefaultAsync<TKey>(LambdaExpression expression) => await db.SelectScalarOrDefaultAsync<TKey>(SumSql<TKey>(expression));
    protected async Task<TKey> AvgOrDefaultAsync<TKey>(LambdaExpression expression) => await db.SelectScalarOrDefaultAsync<TKey>(AvgSql<TKey>(expression));
    #endregion
    #endregion

    #region 执行
    #region 同步
    protected Page<Dto> SelectPage<Dto>(DBAccess db, string sql)
    {
        var (list, count) = db.SelectMultiple(sql, reader => reader.ReadList<Dto>(), reader => reader.ReadScalar<long>());
        return new Page<Dto>
        {
            List = list,
            TotalCount = count
        };
    }
    protected Dto First<Dto>(LambdaExpression expression)
    {
        var sql = this.Limit(1).ToSqlList(expression);
        var model = db.SelectModel<Dto>(sql);
        return model;
    }
    protected Dto FirstOrDefault<Dto>(LambdaExpression expression)
    {
        var sql = this.Limit(1).ToSqlList(expression);
        var model = db.SelectModelOrDefault<Dto>(sql);
        return model;
    }
    protected List<Dto> ToList<Dto>(LambdaExpression expression)
    {
        var sql = ToSql(EnumSelectToSql.ToList, expression);
        var list = db.SelectModelList<Dto>(sql);
        return list;
    }
    protected Page<Dto> ToPage<Dto>(LambdaExpression expression)
    {
        var sql = ToSql(EnumSelectToSql.ToPage, expression);
        return SelectPage<Dto>(db, sql);
    }
    protected Page<Dto> ToPage<Dto>(LambdaExpression expression, int pageIndex, int pageSize)
        => this.Page(pageIndex, pageSize).ToPage<Dto>(expression);
    public long Count()
    {
        var sql = ToSql(EnumSelectToSql.Count, null);
        return db.SelectScalar<long>(sql);
    }
    #endregion

    #region 异步
    protected async Task<Page<Dto>> SelectPageAsync<Dto>(DBAccess db, string sql)
    {
        var (list, count) = await db.SelectMultipleAsync(sql, async reader => await reader.ReadListAsync<Dto>(), async reader => await reader.ReadScalarAsync<long>());
        return new Page<Dto>
        {
            List = list,
            TotalCount = count
        };
    }
    protected async Task<Dto> FirstAsync<Dto>(LambdaExpression expression)
    {
        var sql = this.Limit(1).ToSqlList(expression);
        var model = await db.SelectModelAsync<Dto>(sql);
        return model;
    }
    protected async Task<Dto> FirstOrDefaultAsync<Dto>(LambdaExpression expression)
    {
        var sql = this.Limit(1).ToSqlList(expression);
        var model = await db.SelectModelOrDefaultAsync<Dto>(sql);
        return model;
    }
    protected async Task<List<Dto>> ToListAsync<Dto>(LambdaExpression expression)
    {
        var sql = ToSql(EnumSelectToSql.ToList, expression);
        var list = await db.SelectModelListAsync<Dto>(sql);
        return list;
    }
    protected async Task<Page<Dto>> ToPageAsync<Dto>(LambdaExpression expression)
    {
        var sql = ToSql(EnumSelectToSql.ToPage, expression);
        var (list, count) = await db.SelectMultipleAsync(sql, async reader =>
        {
            return await reader.ReadListAsync<Dto>();
        }, async reader => await reader.ReadScalarAsync<long>());
        return new Page<Dto>
        {
            List = list,
            TotalCount = count
        };
    }
    protected async Task<Page<Dto>> ToPageAsync<Dto>(LambdaExpression expression, int pageIndex, int pageSize)
        => await this.Page(pageIndex, pageSize).ToPageAsync<Dto>(expression);
    public async Task<long> CountAsync()
    {
        var sql = ToSql(EnumSelectToSql.Count, null);
        return await db.SelectScalarAsync<long>(sql);
    }
    #endregion
    #endregion        
}
public class GroupBuilderBase
{
    protected LambdaExpression groupExpression = null;
    protected readonly SelectBuilderBase selectBuilder = null;
    protected readonly DBAccess db = null;
    protected List<object> havings = [];
    internal GroupBuilderBase(SelectBuilderBase selectBuilder, LambdaExpression groupExpression)
    {
        this.groupExpression = groupExpression;
        this.selectBuilder = selectBuilder;
        this.db = selectBuilder.db;
    }

    #region 分页
    private int? pageIndex = null;
    private int? pageSize = null;
    private int? limitSize = null;
    private int? limitStartIndex = null;
    public virtual GroupBuilderBase Page(int pageIndex, int pageSize)
    {
        AssertUtil.BeTrue(pageIndex >= 1, $"{nameof(pageIndex)}必须从1开始!");
        AssertUtil.BeTrue(pageSize > 0, $"{nameof(pageSize)}必须大于0!");
        this.pageIndex = pageIndex;
        this.pageSize = pageSize;
        this.limitSize = this.limitStartIndex = null;
        return this;
    }

    private GroupBuilderBase limit(int size, int startIndex)
    {
        AssertUtil.BeTrue(startIndex >= 0, $"{nameof(startIndex)}必须大于等于0!");
        AssertUtil.BeTrue(size >= 0, $"{nameof(size)}必须大于等于0!");
        this.limitSize = size;
        this.limitStartIndex = startIndex;
        this.pageIndex = this.pageSize = null;
        return this;
    }
    public virtual GroupBuilderBase Limit(int size) => this.limit(size, 0);
    public virtual GroupBuilderBase Limit(int startIndex, int size) => this.limit(size, startIndex);
    #endregion

    #region 排序
    private List<(object obj, bool desc)> orderBys = [];
    protected virtual GroupBuilderBase OrderBy(LambdaExpression expression)
    {
        orderBys.Add((expression, false));
        return this;
    }

    protected virtual GroupBuilderBase OrderByDesc(LambdaExpression expression)
    {
        orderBys.Add((expression, true));
        return this;
    }

    /// <summary>
    /// 排序,如: select.Order("age desc,id")
    /// </summary>
    public virtual GroupBuilderBase Order(string orderSeg)
    {
        orderBys.Add((orderSeg, false));
        return this;
    }
    #endregion

    #region ToSql
    protected string ToSql(EnumSelectToSql enumSelectToSql, LambdaExpression expression)
    {
        /*
        select t.clsid ,count(1) 
        from testtbl t 
        where t.id>100
        group by t.clsid 
        having count(1)>1
        order by count(1)
        limit 10
        */
        var sb = new StringBuilder();
        var alias = selectBuilder.EntityAliases;
        var aliasMap = alias.Select((alias, idx) => new KeyValuePair<ParameterExpression, string>(groupExpression.Parameters[idx], alias.Alias));

        //group by
        var groupBySeg = "";
        var dic = new Dictionary<string, string>();
        if (groupExpression.Body.NodeType == ExpressionType.MemberAccess)
        {
            //GroupBy(i=>i.Tyle)
            var member = groupExpression.Body as MemberExpression;
            var seg = BuilderHelper.ParseSql(member, db, aliasMap, groupExpression.Parameters);
            groupBySeg = seg;
            dic.Add("DBUtil.Builders.IGroupFilter.Key", seg);
        }
        else if (groupExpression.Body.NodeType == ExpressionType.New)
        {
            //GroupBy(i=>new{i.Name,i.Age}
            var newExp = groupExpression.Body as NewExpression;
            var args = newExp.Arguments;
            var members = newExp.Members;
            var list = new List<string>();
            for (int i = 0; i < args.Count; i++)
            {
                var arg = args[i];
                //expr中的这个属性可能是常量
                var _val = BuilderHelper.ParseSql(arg, db, aliasMap, groupExpression.Parameters);
                list.Add(_val);
                dic.Add("DBUtil.Builders.IGroupFilter.Key." + members[i].Name, _val);
            }
            groupBySeg = list.ToStringSeparated(",");
        }

        //select
        switch (enumSelectToSql)
        {
            case EnumSelectToSql.Count:
                sb.Append("select count(1)");
                break;
            case EnumSelectToSql.ToList:
            case EnumSelectToSql.ToPage:
                var val = BuilderHelper.ParseSql(expression, db, aliasMap, null, dic);
                sb.Append("select ").Append(val);
                break;
        }

        //from
        var fromSql = selectBuilder.GeneFromSql();
        sb.AppendLine().Append(fromSql);
        //where 过滤
        var whereSql = selectBuilder.GeneWhereSql();
        if (whereSql.IsNotNullOrWhiteSpace()) sb.AppendLine().Append(whereSql);
        //group by
        sb.AppendLine().Append("group by ").Append(groupBySeg);
        //having 过滤
        var havingSql = GetHavingSql(aliasMap, dic);
        if (havingSql.IsNotNullOrWhiteSpace()) sb.AppendLine().Append(havingSql);
        //order by
        var orderSql = GetOrderSql(aliasMap, dic);
        if (orderSql.IsNotNullOrWhiteSpace()) sb.AppendLine().Append(orderSql);

        //limit
        var limitSeg = GetLimit();
        if (limitSeg.IsNotNullOrWhiteSpace()) sb.AppendLine().Append(limitSeg);
        var countSql = string.Empty;
        if (enumSelectToSql == EnumSelectToSql.ToPage)
        {
            var sb2 = new StringBuilder();
            sb2.Append("select count(1)").AppendLine().Append(fromSql);
            if (whereSql.IsNotNullOrWhiteSpace()) sb2.AppendLine().Append(whereSql);
            sb2.AppendLine().Append("group by ").Append(groupBySeg);
            if (havingSql.IsNotNullOrWhiteSpace()) sb2.AppendLine().Append(havingSql);
            if (orderSql.IsNotNullOrWhiteSpace()) sb2.AppendLine().Append(orderSql);
            sb2.Append(';');
            countSql = sb2.ToString();
            sb2.Clear();
        }

        sb.Append(';');
        if (countSql.IsNotNullOrWhiteSpace()) sb.AppendLine().Append(countSql);
        var sql = sb.ToString();
        sb.Clear();
        return sql;
    }
    internal string GetLimit()
    {
        var startIndex = this.limitStartIndex >= 0 ? this.limitStartIndex : this.pageSize > 0 ? ((this.pageIndex - 1) * this.pageSize) : -1;
        var pageSize = this.limitStartIndex >= 0 ? this.limitSize : this.pageSize > 0 ? this.pageSize : -1;
        if (startIndex > 0)
        {
            return $"limit {startIndex},{pageSize}";
        }
        else if (startIndex == 0)
        {
            return $"limit {pageSize}";
        }
        return null;
    }
    internal string GetOrderSql(IEnumerable<KeyValuePair<ParameterExpression, string>> aliasMap, Dictionary<string, string> dic)
    {
        if (this.orderBys.IsNotNullOrEmpty())
        {
            var segs = new List<string>();
            for (int i = 0; i < this.orderBys.Count; i++)
            {
                var orderBy = this.orderBys[i];
                if (orderBy.obj is string str)
                {
                    segs.Add(str);
                    continue;
                }
                var isDesc = this.orderBys[i].desc;
                var lambda = orderBy.obj as LambdaExpression;
                var orderSql = BuilderHelper.ParseSql(lambda, db, aliasMap, null, dic);
                segs.Add(orderSql);
            }
            return $"order by {segs.ToStringSeparated(",")}";
        }
        return string.Empty;
    }
    internal string GetHavingSql(IEnumerable<KeyValuePair<ParameterExpression, string>> aliasMap, Dictionary<string, string> dic)
    {
        var filterSqls = new List<string>();
        foreach (var filter in havings)
        {
            if (filter is string str && str.IsNotNullOrWhiteSpace())
            {
                filterSqls.Add(str);
                continue;
            }
            if (filter is not LambdaExpression lambda) throw new Exception($"必须为LambdaExpression!");
            var (exp, midValues) = ExpressionHelper.ReduceLambda(lambda, isKeepCallBack: BuilderHelper.IsKeepCallBackForSubSelect);
            if (exp.NodeType == ExpressionType.Constant)
            {
                var b = (exp as ConstantExpression).Value.To<bool>();
                if (b) filterSqls.Add("1=1");
                else filterSqls.Add("1=0");
            }
            else if (exp is ParameterExpression parameter && midValues.TryGetValue(parameter, out object _val))
            {
                var b = _val.To<bool>();
                if (b) filterSqls.Add("1=1");
                else filterSqls.Add("1=0");
            }
            else
            {
                lambda = exp as LambdaExpression;
                string tmp = string.Empty;

                tmp = BuilderHelper.ParseSql(lambda, db, aliasMap, null, dic, midValues: midValues);
                if (tmp.IsNotNullOrWhiteSpace()) filterSqls.Add(tmp);
            }
        }

        if (filterSqls.IsNotNullOrEmpty())
        {
            if (filterSqls.Count == 1) return $"having {filterSqls[0]}";
            else return $"having {filterSqls.Select(i => $"({i})").ToStringSeparated(" and ")}";
        }
        return string.Empty;

    }

    protected string ToSqlFirst(LambdaExpression expression) => Limit(1).ToSql(EnumSelectToSql.ToList, expression);
    protected string ToSqlList(LambdaExpression expression) => ToSql(EnumSelectToSql.ToList, expression);
    protected string ToSqlPage(LambdaExpression expression) => ToSql(EnumSelectToSql.ToPage, expression);
    protected string ToSqlPage(LambdaExpression expression, int pageIndex, int pageSize) => Page(pageIndex, pageSize).ToSql(EnumSelectToSql.ToPage, expression);
    public virtual string ToSqlCount() => ToSql(EnumSelectToSql.Count, null);
    #endregion

    #region 执行
    #region 同步
    protected Dto First<Dto>(LambdaExpression expression)
    {
        AssertUtil.NotNull(expression);
        var sql = this.Limit(1).ToSqlList(expression);
        var model = db.SelectModel<Dto>(sql);
        return model;
    }
    protected Dto FirstOrDefault<Dto>(LambdaExpression expression)
    {
        AssertUtil.NotNull(expression);
        var sql = this.Limit(1).ToSqlList(expression);
        var model = db.SelectModelOrDefault<Dto>(sql);
        return model;
    }
    protected List<Dto> ToList<Dto>(LambdaExpression expression)
    {
        AssertUtil.NotNull(expression);
        var sql = this.ToSqlList(expression);
        var model = db.SelectModelList<Dto>(sql);
        return model;
    }
    protected Page<Dto> ToPage<Dto>(LambdaExpression expression)
    {
        var sql = ToSql(EnumSelectToSql.ToPage, expression);
        var (list, count) = db.SelectMultiple(sql, reader =>
        {
            return reader.ReadList<Dto>();
        }, reader => reader.ReadScalar<long>());
        return new Page<Dto>
        {
            List = list,
            TotalCount = count
        };
    }
    protected Page<Dto> ToPage<Dto>(LambdaExpression expression, int pageIndex, int pageSize)
    {
        return this.Page(pageIndex, pageSize).ToPage<Dto>(expression);
    }
    public long Count()
    {
        var sql = ToSql(EnumSelectToSql.Count, null);
        return db.SelectScalar<long>(sql);
    }
    #endregion
    #region 异步
    protected async Task<Dto> FirstAsnc<Dto>(LambdaExpression expression)
    {
        AssertUtil.NotNull(expression);
        var sql = this.Limit(1).ToSqlList(expression);
        var model = await db.SelectModelAsync<Dto>(sql);
        return model;
    }
    protected async Task<Dto> FirstOrDefaultAsnc<Dto>(LambdaExpression expression)
    {
        AssertUtil.NotNull(expression);
        var sql = this.Limit(1).ToSqlList(expression);
        var model = await db.SelectModelOrDefaultAsync<Dto>(sql);
        return model;
    }
    protected async Task<List<Dto>> ToListAsync<Dto>(LambdaExpression expression)
    {
        AssertUtil.NotNull(expression);
        var sql = this.ToSqlList(expression);
        var model = await db.SelectModelListAsync<Dto>(sql);
        return model;
    }
    protected async Task<Page<Dto>> ToPageAsync<Dto>(LambdaExpression expression)
    {
        var sql = ToSql(EnumSelectToSql.ToPage, expression);
        var (list, count) = await db.SelectMultipleAsync(sql, async reader =>
        {
            //todo: 使用特定的匹配规则 而不是先到 entity 再转dto 也不是直接读取到dto
            return await reader.ReadListAsync<Dto>();
        }, async reader => await reader.ReadScalarAsync<long>());
        return new Page<Dto>
        {
            List = list,
            TotalCount = count
        };
    }
    protected async Task<Page<Dto>> ToPageAsync<Dto>(LambdaExpression expression, int pageIndex, int pageSize)
    {
        return await this.Page(pageIndex, pageSize).ToPageAsync<Dto>(expression);
    }
    public async Task<long> CountAsync()
    {
        var sql = ToSql(EnumSelectToSql.Count, null);
        return await db.SelectScalarAsync<long>(sql);
    }
    #endregion
    #endregion
}